package dataconnect;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * description: Investment
 * 项目的sql操作
 * 
 * @author what-is-me
 * @date 2022/1/3 23:07
 */
public class Investment {
    // 项目名、所投单位、投资开始日期、预期结束日期、投资金额、年收益
    public static final String tag[] = { "项目名", "所投单位", "投资开始日期", "预期结束日期", "投资金额($)",
            "年收益(%)" },
            tg[] = { "Name", "Company", "BeginDate", "EndDate", "Amount", "YearlyEarnings" };
    Statement st;

    public Investment(Statement st) throws SQLException {
        /**
         * Description: Investment
         * 
         * @param st:java.sql.Statement
         * @return {@link null}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:23
         */
        this.st = st;
        this.st.execute(
                "CREATE TABLE IF NOT EXISTS `data` (`Name` VARCHAR(100),`Company` TEXT NOT NULL,`BeginDate` DATE NOT NULL,`EndDate` DATE NOT NULL,`Amount` DECIMAL(15,2) NOT NULL,`YearlyEarnings` DECIMAL(15,2) NOT NULL,PRIMARY KEY (`Name`))ENGINE=InnoDB DEFAULT CHARSET=utf8;");
    }

    public boolean add(String[] args) {
        /**
         * Description: add
         * 
         * @param args:java.lang.String
         * @return {@link boolean}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:23
         */
        String in = new String(
                "INSERT INTO data(`Name`,`Company`,`BeginDate`,`EndDate`,`Amount`,`YearlyEarnings`) VALUES(");
        for (int i = 0; i < 6; i++) {
            if (args[i].equals(""))
                return false;
            in += "'" + args[i].strip() + "'";
            if (i != 5)
                in += ",";
        }
        in += ");";
        try {
            st.execute(in);
        } catch (SQLException se) {
            return false;
        }
        return true;
    }

    @Deprecated
    public boolean update(String[] args) {
        /**
         * Description: update
         * 
         * @param args:java.lang.String
         * @return {@link boolean}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:23
         */
        String in = new String("UPDATE data SET");
        boolean isempty = true;
        for (int i = 1; i < 6; i++) {
            args[i] = args[i].strip();
            if (args[i].equals(""))
                return false;
            if (!isempty)
                in += ",";
            in += tag[i] + "='" + args[i] + "'";
            isempty = false;
        }
        in += " WHERE Name='" + args[0] + "';";
        if (args[0].equals(""))
            return false;
        try {
            st.execute(in);
        } catch (SQLException se) {
            return false;
        }
        return true;
    }

    private String[][] transfer(ResultSet e) throws SQLException {
        /**
         * Description: transfer
         * 将ResultSet改成数组
         * 
         * @param e:java.sql.ResultSet
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:23
         */
        List<String[]> ret = new ArrayList<>();
        while (e.next()) {
            String[] tmp = new String[6];
            for (int i = 0; i < 6; i++) {
                tmp[i] = e.getString(tg[i]);
            }
            ret.add(tmp);
        }
        int n = ret.size(), i = 0;
        String[][] r = new String[n][];
        for (String[] tmp : ret)
            r[i++] = tmp;
        return r;
    }

    public String[][] queryName(String[] args) {
        /**
         * Description: queryName
         * 
         * @param args:java.lang.String
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:24
         */
        try {
            return transfer(st.executeQuery("SELECT * FROM data WHERE Name='" + args[0] + "';"));
        } catch (SQLException e) {
            return null;
        }
    }

    public String[][] queryYear(String[] args) {
        /**
         * Description: queryYear
         * 
         * @param args:java.lang.String
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:24
         */
        try {
            return transfer(st.executeQuery("SELECT * FROM data WHERE YEAR(BeginDate)='" + args[0] + "';"));
        } catch (SQLException e) {
            return null;
        }
    }

    public String[][] queryBeforeNow() {
        /**
         * Description: queryBeforeNow
         * 
         * @param :
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:24
         */
        try {
            return transfer(st.executeQuery("SELECT * FROM data WHERE EndDate<DATE(NOW());"));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public String[][] queryAfterNow() {
        /**
         * Description: queryAfterNow
         * 
         * @param :
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:24
         */
        try {
            return transfer(st.executeQuery("SELECT * FROM data WHERE EndDate>=DATE(NOW());"));
        } catch (SQLException e) {
            return null;
        }
    }

    public String[][] queryEarning() {
        /**
         * Description: queryEarning
         * 假设收益为天数乘年收益/365
         * 
         * @param :
         * @return {@link String[][]}
         * @throws
         * @author what-is-me
         * @date 2022/1/3 23:24
         */
        String[][] ret = queryBeforeNow();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (String[] row : ret) {
            double y = Double.valueOf(row[5]), a = Double.valueOf(row[4]);
            try {
                long days = (sdf.parse(row[3]).getTime() - sdf.parse(row[2]).getTime()) / ((long) 1000 * 3600 * 24);
                row[5] = String.format("%.2f", y * days / 36500.0 * a);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return ret;
    }
}
